"""
Case Type   : 防篡改
Case Name   : 三权分立关闭时验证普通用户对模式没有任何权限在授予usage权限之后拥有usage权限
Create At   : 2022-02-24
Owner       : lwx1080719
Description :
    1.查询三权分立参数enableSeparationOfDuty
    2.创建用户及数据库
    3.给普通用户赋予权限
    4.用户1登录数据库创建模式及表
    5.无任何权限2登录数据库DML普通表
    6.用户1登录数据库赋权限给用户2
    7.赋权限后用户2登录数据库DML普通表
    8.清理环境
Expect      :
    1.显示默认值off
    2.成功
    3.权限赋予成功
    4.成功
    5.失败
    6.成功
    7.失败
    8.成功
History     : 
"""



import os
import unittest
from yat.test import macro
from yat.test import Node
from testcase.utils.Constant import Constant
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Common import Common
from testcase.utils.Logger import Logger

class ModifyCase(unittest.TestCase):
    def setUp(self):
        self.logger = Logger()
        self.logger.info(f'-----{os.path.basename(__file__)} start-----')
        self.dbuserNode = Node('PrimaryDbUser')
        self.primary_sh = CommonSH('PrimaryDbUser')
        self.Constant = Constant()
        self.Common = Common()
        self.user_name = 'u_security_usage_privilege_0002'
        self.table_name = 't_security_usage_privilege_0002'
        self.database_name = 'd_security_usage_privilege_0002'
        self.schema_name = 's_security_usage_privilege_0002'
        self.default_value = self.Common.show_param('enableSeparationOfDuty')

    def test_security(self):
        text = '----step1:查询三权分立参数enableSeparationOfDuty值  ' \
               'expect:默认值off----'
        self.logger.info(text)
        self.assertEqual("off", self.default_value, "执行失败:" + text)

        text = '----step2:创建用户及数据库 expect:成功----'
        self.logger.info(text)
        sql_cmd = self.primary_sh.execut_db_sql(f'''
            create user {self.user_name}_1 createrole password 
            '{macro.PASSWD_REPLACE}';
            create user {self.user_name}_2  password '{macro.PASSWD_REPLACE}';
            create database {self.database_name};''')
        self.logger.info(sql_cmd)
        self.assertTrue(
            sql_cmd.count(self.Constant.CREATE_ROLE_SUCCESS_MSG) == 2 and
            sql_cmd.count(self.Constant.CREATE_DATABASE_SUCCESS) == 1,
            "执行失败:" + text)

        text = '----step3:给普通用户1赋权限 expect:成功----'
        self.logger.info(text)
        sql_cmd = self.primary_sh.execut_db_sql(f'''
            grant create on database {self.database_name} 
            to {self.user_name}_1;''')
        self.logger.info(sql_cmd)
        self.assertTrue(
            sql_cmd.count(self.Constant.GRANT_SUCCESS_MSG) == 1,
            "执行失败:" + text)

        text = '----step4:用户1登录数据库创建模式及表 expect:成功----'
        self.logger.info(text)
        sql_cmd = f'''create schema {self.schema_name};
            create table {self.schema_name}.{self.table_name}
            (id int primary key,name varchar(100));'''
        self.logger.info(sql_cmd)
        msg = self.primary_sh.execut_db_sql(
            sql_cmd, sql_type=f' -U {self.user_name}_1 '
            f'-W {macro.PASSWD_REPLACE}',
            dbname=f'{self.database_name}')
        self.logger.info(msg)
        self.assertTrue(
            msg.count(self.Constant.CREATE_TABLE_SUCCESS) == 2  and
            msg.count(self.Constant.CREATE_SCHEMA_SUCCESS_MSG) == 1,
            "执行失败:" + text)

        text = '----step5:无任何权限的用户2登录数据库DML普通表 expect:失败----'
        self.logger.info(text)
        sql_cmd = f'''
            insert into {self.schema_name}.{self.table_name} 
            values(1,'beijing'),(2,'shanghai');
            select * from {self.schema_name}.{self.table_name};
            delete from {self.schema_name}.{self.table_name} where id=2;
            update  {self.schema_name}.{self.table_name} 
            set name='shanxi' where id=1;
            truncate table {self.schema_name}.{self.table_name};'''
        self.logger.info(sql_cmd)
        msg = self.primary_sh.execut_db_sql(
            sql_cmd, sql_type=f' -U {self.user_name}_2 '
            f'-W {macro.PASSWD_REPLACE}',
            dbname=f'{self.database_name}')
        self.logger.info(msg)
        self.assertTrue(
            msg.count(self.Constant.PERMISSION_DENIED) == 5,
            "执行失败:" + text)

        text = '----step6:用户1登录数据库赋权限给用户2 expect:成功----'
        self.logger.info(text)
        sql_cmd = f'''
            grant usage on schema {self.schema_name} to {self.user_name}_2;'''
        self.logger.info(sql_cmd)
        msg = self.primary_sh.execut_db_sql(
            sql_cmd, sql_type=f' -U {self.user_name}_1 '
            f'-W {macro.PASSWD_REPLACE}',
            dbname=f'{self.database_name}')
        self.logger.info(msg)
        self.assertIn(self.Constant.GRANT_SUCCESS_MSG,  msg,
                      "执行失败:" + text)

        text = '----step7:赋权限后用户2登录数据库DML普通表 expect:失败----'
        self.logger.info(text)
        sql_cmd = f'''
            insert into {self.schema_name}.{self.table_name} 
            values(1,'beijing'),(2,'shanghai');
            select * from {self.schema_name}.{self.table_name};
            delete from {self.schema_name}.{self.table_name} where id=2;
            update  {self.schema_name}.{self.table_name} 
            set name='shanxi' where id=1;
            truncate table {self.schema_name}.{self.table_name};'''
        self.logger.info(sql_cmd)
        msg = self.primary_sh.execut_db_sql(
            sql_cmd, sql_type=f' -U {self.user_name}_2 '
            f'-W {macro.PASSWD_REPLACE}',
            dbname=f'{self.database_name}')
        self.logger.info(msg)
        self.assertTrue(
            msg.count(self.Constant.PERMISSION_DENIED) == 5,
            "执行失败:" + text)

    def tearDown(self):
        text = '----step8:清理环境 expect:成功----'
        self.logger.info(text)
        sql_cmd = self.primary_sh.execut_db_sql(f'''
            drop database {self.database_name};
            drop user {self.user_name}_1 cascade;
            drop user {self.user_name}_2 cascade;''')
        self.logger.info(sql_cmd)
        self.assertTrue(
            sql_cmd.count(self.Constant.DROP_ROLE_SUCCESS_MSG) == 2 and
            sql_cmd.count(self.Constant.DROP_DATABASE_SUCCESS) == 1,
            "执行失败:" + text)
        self.logger.info(f'-----{os.path.basename(__file__)} end-----')
